# -*-  coding: UTF-8  -*-

import wx
import sqlite3
import datetime, calendar
import cfg

def DT2Sql(dt) :
    """datetime to SQL date transformation with hours
    return a couple ("YYYY-MM-DD", "HH:mm")"""
    assert isinstance(dt, datetime.datetime)
    return (str(dt.date()), str(dt.time())[:5])

def Sql2DT(d) :
    """SQL date to datetime transformation"""
    ymd = d.split('-')
    return datetime.datetime(int(ymd[0]), int(ymd[1]), int(ymd[2]))


class ActivitiesError(Exception) :
    pass


class Activity(object) :
    def __init__(self, row) :
        self.id_activity  = row[0]
        self.date         = row[1]
        self.when         = row[2]
        self.activity     = row[3]


class ActivityDB(object) :
    """Represent the interface SQLite"""

    CREATE_TABLE_ACTIVITIES = """
        create table if not exists activities (
            id       integer  primary key  autoincrement,
            date     text,
            time     integer,
            name     text,
            unique(date, time)
        )"""

    CREATE_TABLE_HOLIDAYS = """
        create table if not exists holidays (
            date text,
            unique(date)
        )"""


    def __init__(self, db) :
        self.cnx = sqlite3.connect(db)
        self.cur = self.cnx.cursor()
        self.cur.execute(self.CREATE_TABLE_ACTIVITIES)
        self.cur.execute(self.CREATE_TABLE_HOLIDAYS)
        self.cnx.commit()

    def __del__(self) :
        self.cur.close()
        self.cnx.close()

    def InsertActivity(self, d, t, a) :
        try :
           self.cur.execute("insert into activities(date, time, name) values(?, ?, ?)", (d, t, a))
           self.cnx.commit()
        except sqlite3.IntegrityError :
            raise ActivitiesError("There is already an activity at this date/time.")

    def GetAllActivities(self) :
       self.cur.execute("select * from activities")
       return [Activity(row) for row in self.cur]

    def GetLastActivity(self) :
       self.cur.execute("select * from activities order by date desc, time desc limit 1")
       row = self.cur.fetchone()
       return Activity(row) if row else None

    def GetByDate(self, d) :
       self.cur.execute("select * from activities where date = ?", (d,))
       return [Activity(row) for row in self.cur]

    def GetBetween(self, d1, d2) :
       self.cur.execute("select * from activities where date>=? and date<=?", (d1, d2))
       return [Activity(row) for row in self.cur]

    def GetAllActivitiesName(self) :
       self.cur.execute("select distinct(name) from activities")
       return [row[0] for row in self.cur]

    def InsertHoliday(self, d) :
        try :
           self.cur.execute("insert into holidays(date) values(?)", (d,))
           self.cnx.commit()
        except sqlite3.IntegrityError :
            raise ActivitiesError("There is already a holiday at this date.")

    def RemoveHoliday(self, d) :
        try :
           self.cur.execute("delete from holidays where date = ?", (d,))
           self.cnx.commit()
        except :
           raise ActivityError("Not possible to remove this holiday !")

    def GetAllHolidays(self) :
       self.cur.execute("select * from holidays order by date desc")
       return [row[0] for row in self.cur]

    def GetFirstDate(self) :
       self.cur.execute("select min(date) from activities")
       row = self.cur.fetchone()
       return row[0] if row else None
